1. Field of the Invention
This invention relates to the manipulation of hierarchical information in a computer system, and more specifically to building an application interface using hierarchical information.
2. Background Art
Software applications designed to run on computer systems allow an application user to manipulate data associated with an application. For example, an accounting application allows a user to manipulate accounting information. A graphical user interface (GUI) is typically designed for a software application that allows an application user to enter and update application data.
Design tools are available that assist an application software developer in designing an application""s GUI. However, design tools that are currently available require that the application developer be aware of relationships that exist in the application data.
For example, application data can be managed by a database management system (DBMS) such as a relational database management system (RDBMS). In an RDBMS, data is stored in tables that can be interrelated.
A table can contain multiple rows of data where each row contains one or more fields or columns. Each column contains an item of data. For example, an employee table contains rows of employee records. Each row, or record, contains information regarding an employee. An employee record can contain, for example, a last name column that contains a last name of the employee.
Data stored in a column (or columns) of a table can form the basis for a relationship between that table and another table in the database having a related column (or columns). Using a relationship between columns of two tables, it is possible to join these two tables to provide a single table of information that contains instances of rows from one table combined with related rows from the other table.
Tables may be related via one-to-one, one-to-many, or many-to-one relationships. In a one-to-one relationship, one row in one table is related to a single row in a second table and vice versa. For example, a row in an employee table that contains information about an employee relates to a salaries table that contains the employee""s salary information. Since an employee is typically only earning a single salary, there is a one-to-one relationship between an employee""s employee table record and the employee""s salary table record.
In a one-to-many relationship, a row in one table may be related to many rows in a second table, but each row in the second table matches only one row in the first table. For example, a state table that contains a state identifier and a state name can be related to multiple rows in the employee table. However, a row in the employees table identifies only one state of residence, for example. Conversely, a many-to-one relationship exists where many rows in one table match only one row in a second table, but each row in the second table may match many rows in the first table.
To relate two tables, it is necessary to identify one or more columns that are common to both tables. These columns are typically referred to as keys. A primary key is a unique key within a table and uniquely identifies a row within the table. A foreign key in a second table is comprised of the column(s) containing a first table""s primary key information. For example, in the employee table, an employee identifier (employeeID) can be assigned to uniquely identify each employee. The employeeID can be used as a primary key for the employees table. The employeeID can also be used as a foreign key in the salaries table. The employees and salaries tables can be joined by the employeeID columns in each table to have information from both tables available in a single record.
Applications are developed to provide a user with the ability to facilitate access and manipulation of the data contained in a DBMS. A DBMS includes a Data Manipulation Language (DML) such as Structured Query Language (SQL). A DML provides set-oriented relational operations for manipulating data in the DBMS. However, a DML requires a precise syntax that must be used to access and manipulate DBMS data. To use a DML, a user must understand and use the DML""s syntax. Instead of requiring each user that wishes to modify data stored in a DBMS to learn the DML""s syntax, applications are written that provide an interface between the user and a DBMS"" DML. For example, a personnel application provides an interface to allow an employee in the personnel department to view and update employee information.
A database application typically includes a graphical user interface (GUI) that is comprised of presentation elements such as a form. A form is self-contained presentation element with predefined areas for displaying, entering and/or changing data. The predefined areas are typically referred to as fields.
Fields in a form can be read-only (e.g., a label field). A form can also have fields in which information is displayed and the user can enter information. For example, in an operation to modify an employee""s last name, a text field can be used to display the name currently stored in the database and allow the user to overwrite the display to enter the employee""s new last name. When user submits the information, the application can retrieve the user input and update the database accordingly.
To populate the fields in a form, the application developer must associate fields in the database with fields in the form. This process is typically referred to as binding database fields with form fields. In a simple example of a form, the fields contained within the form are bound to fields in a single table.
A form can be more complicated where fields receive data from multiple tables. The application developer designing a form must be aware of the relationship(s) that exist between the tables that are to be bound to the fields in the form. If, for example, the form contains fields from tables that are related via a one-to-many relationship, the application developer must design the form the accommodate multiple rows of information. For example, a form that displays a department and its joblistings (which are related via a one-to-many relationship) must be able to display the department information as well as multiple joblistings for that department. The form must be designed to display the department and include multiple rows to display the joblistings for the department, for example.
The relationship between the department and joblistings tables is a one-to-many relationship. That is, there can be many joblistings for a given department. The format of the display of information related by a one-to-many relationship is different than that used to display information related by either a many-to-one or one-to-one relationship.
For example, the employees, salaries and state tables are related to each other via many-to-one (e.g., more than one employee can reside in the same state) and one-to-one relationship (e.g., each employee has a salary). Since a row in the employees table is related to a single row in state and salaries tables, the developer can design a form with fields that display information about an employee taken from these three tables. For example, the form can contain fields to display the employee""s name, state of residence and salary.
Thus, it can be seen that the design of a form depends on the type of relationships that exist between the tables. Using existing interface design tools, the application developer has been required to have knowledge of these relationships. The application developer must be aware of the structure of a database including the tables, the columns within a table and the relationships between tables. For example, an application developer that is building a form to display output retrieved from a database must associate fields in the form with columns in the database. If the form includes columns from multiple tables, the developer must be aware of the relationships that exist between the tables which includes the type of relationship.
One example of such a design tool for database applications is the Access product which is available from Microsoft Corporation. Access allows a developer to define the structure of a database and to create forms with fields that are tied to fields in the database. To develop a form, the developer selects a table and fields from the table in the database to include in the form. However, Access does not show relationships between tables. The developer must be aware of the relationships that exist between tables in a database to design an application""s interface. This requires that the application developer become knowledgeable as to the structure of the database. This is inefficient. Further, there is the possibility that an. application developer will err in generating an interface that cannot accommodate the relationships that exist between the data to be displayed in fields in the interface.
Embodiments of the invention comprise a design tool for designing an application interface. The design tool includes a graphical user interface (GUI) that visually represents a hierarchy of data and the relationships between the data. Thus, the design tool eliminates the need for an interface designer to have independent knowledge of the structure of the data (i.e., the data fields and relationships between the data). The structure of the data is presented to the interface designer in the design tool""s display.
The design tool""s GUI represents the data and the relationships between the data in a hierarchical display referred to as a data palette. An output hierarchy comprised of output levels is created as the user selects fields from the data palette to be displayed in the application""s interface. When a data field is selected, the design tool automatically determines the appropriate output level of the output hierarchy using the relationships defined for the data. The design tool further identifies an appropriate interface component within an output level for the selected field.
The design tool""s GUI can optionally include a display that identifies the selected fields and the output levels created in the output hierarchy. Indentations in the display reflect the output levels. Further, the optional display identifies the type of interface component in which a selected field is included for review by the interface designer.
Output levels are associated with interface components that comprise the application""s interface. Examples of interface components that can be used in the application""s interface include a form, view and subband. Multiple interface components can be contained within a single output level. Interface components are comprised of interface objects such as text fields and buttons. A view is comprised of one or more lines, or bands, of information. Each band is comprised of one or more columns, or fields, of data. A subband is comprised of one or more columns of data.
An interface component can be nested within another interface component to display levels of detail in the data. For example, a subband can be nested within a view which is nested within a form. The design tool associates an interface component with an output level and displays the association in the design tool""s GUI display.
The output hierarchy is created based on the data relationships that exist between the data that is selected by the user. A first level of the output hierarchy displays data from fields contained in a primary database table that is selected by the user. The level can further contain fields from tables that are related to the primary table via a one-to-one relationship or a many-to-one relationship. If the user selects a field from a table that is related to the primary table via a one-to-many relationship, the design tool creates another output level that is a child of the top-most output level. A link is automatically created between an interface component in the child and an interface component in the parent output level. The link can be used to perform operations on the data (e.g., update and retrieval operations).
An interface designer selects a database table as a primary table and an interface component for the first output level. The design tool generates a table hierarchy with the primary table as the root node. Nodes in the table hierarchy represent database tables and branches between the nodes represent the relationships between the tables.
The data palette can be generated using the table hierarchy. Initially, the data palette includes entries for the primary table, its fields, and its relationships with other tables. The user can expand (or reduce) a relationship to view a related table, its fields, and its relations.
When a field is selected in the data palette, embodiments of the invention traverse the table hierarchy to identify an output level for the selected field. If the selected field is contained within a table that is related to the primary table via a one-to-one or many-to-one relationship, the selected field can be added to the current output level. If the selected field is contained within a table that is related to the primary table via a one-to-many relationship, the selected field is added to a child output level of the primary table""s output level. If the child output level is new, a link is automatically created between the primary table""s output level and the related table. If necessary, an interface component is created within the child output level for the selected field.